<?php

App::uses('AppModel', 'Model');

/**
 * Description of Reports
 *
 * @author FAdugyamfi
 */
class Report extends AppModel {
    //put your code here

    public $useDbConfig = "organisations";
    
    /**
     * belongsTo associations
     *
     * @var array
     */
    public $belongsTo = array(
        'ReportCategory' => array(
            'className' => 'ReportCategory',
            'foreignKey' => 'report_category_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );

    /**
     * belongsTo associations
     *
     * @var array
     */
    public $hasMany = array(
        'ReportParameter' => array(
            'className' => 'ReportParameter',
            'foreignKey' => 'report_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
        'ReportColumn' => array(
            'className' => 'ReportColumn',
            'foreignKey' => 'report_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );

    public function generate($id, $data = array()) {
        $resultArray = $this->find('first', array(
            'contain' => array('ReportColumn' => array('ReportColumnOption')),
            'conditions' => array(
                'Report.id' => $id
            )
        ));
        
        $filename = ROOT . DS . 'dbase/reports/' . $resultArray['Report']['query_file'];
        $report_query = file_get_contents($filename);

        $dataSource = $this->getDataSource();

        $search = array_keys($data);
        $replace = $this->escapeValues($data);
        $query = str_replace($search, $replace, $report_query);

        $allQueries = explode(';', $query);
        
        $dataSource->begin($this);

        try {
            $resultArray['Results'] = array();
            foreach ($allQueries as $q) {
                if (trim($q)) {
                    $result = $this->query(trim($q));
                   
                    if( is_array($result) ) {
                        $resultArray['Results'] = array_merge($resultArray['Results'], $result);
                    }
                }
            }

            $dataSource->commit($this);
            
        } catch (Exception $e) {
            $dataSource->rollback();
        }

        return $resultArray;
    }

    public function escapeValues($array) {
        $returnData = array();
        foreach ($array as $str) {

            $str = mysql_escape_string($str);
            $returnData[] = " '" . $str . "' ";
        }
        return $returnData;
    }

}
